package com.kaikeba.dao.impl;

import com.kaikeba.bean.Courier;
import com.kaikeba.bean.User;
import com.kaikeba.dao.BaseUserDao;
import com.kaikeba.util.DruidUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: 平川
 * @Description:
 * @Date Created in 2021-08-02 21:12
 * @Modified By:
 */
public class UserDaoMysql implements BaseUserDao {
    public static final String SQL_CONSOLE="SELECT COUNT(*) data_size,COUNT(TO_DAYS(intime)=TO_DAYS(NOW())OR NULL ) data_day FROM user";
    //用于查询所有的数据库信息
    public static final String SQL_FIND_ALL="SELECT * FROM user";
    //用于分页查询所有的数据库信息
    public static final String SQL_FIND_LIMIT="SELECT * FROM user LIMIT ?,?";
    //通过id查询
    public static final String SQL_FIND_BY_ID="SELECT*FROM user WHERE ID =?";
    //通过手机号码查询
    public static final String SQL_FIND_BY_phonenumber="SELECT*FROM user WHERE phonenumber =?";

    //插入
    public static final String SQL_INERT="INSERT INTO user(USERNAME,PHONENUMBER,PASSWORD,CARDID,INTIME,OUTTIME) VALUES(?,?,?,?,NOW(),now())";
    //更新
    public static final String SQL_UPDATE="UPDATE user SET username=?,phonenumber=?,cardid=?,PASSWORD=? WHERE ID=?" ;
    //根据手机号更新最近登陆时间
    public static final String SQL_UPDATETIME="UPDATE USER SET OUTTIME=NOW() WHERE phonenumber=? ";
    //根据id 去修改用户名与手机号
    public static final String SQL_UPDATE_USERNAME_USERPHONE="update user set username=?,phonenumber=? where id=?";
    //根据id 去修改用户名与手机号,密码
    public static final String SQL_UPDATE_USERNAME_USERPHONE_PASSWORD="update user set username=?,phonenumber=?,password=? where id=?";
    //删除
    public static final String SQL_DELETE="DELETE FROM  user WHERE id=?";

    /**
     * @return 用户的人数
     */
    @Override
    public List<Map<String, Integer>> console() {
        List<Map<String,Integer>> list = new ArrayList<>();
        Connection conn = DruidUtil.getConnection();
        PreparedStatement pre = null;
        ResultSet res = null;
        try {
            pre = conn.prepareStatement(SQL_CONSOLE);
            res = pre.executeQuery();
            while (res.next()){
                int data_size = res.getInt("data_size");
                int data_day = res.getInt("data_day");
                Map<String, Integer> map = new HashMap<>();
                map.put("data_size",data_size);
                map.put("data_day",data_day);
                list.add(map);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,pre,res);
        }
        return list;
    }

    /**
     * @param limit
     * @param offset
     * @param pageNumber
     * @return 用户的集合
     */
    @Override
    public List<User> findAll(boolean limit, int offset, int pageNumber) {
        List<User> list = new ArrayList<>();
        Connection conn = DruidUtil.getConnection();
        PreparedStatement pre = null;
        ResultSet res = null;
        try{
            if (limit ){
                pre = conn.prepareStatement(SQL_FIND_LIMIT);
                pre.setObject(1,offset);
                pre.setObject(2,pageNumber);
            }else{
                pre = conn.prepareStatement(SQL_FIND_ALL);
            }
            res = pre.executeQuery();
            while (res.next()){
                int id = res.getInt("id");
                String username = res.getString("username");
                String phonenumber = res.getString("phonenumber");
                String cardid = res.getString("cardid");
                String password = res.getString("password");
                Timestamp intime = res.getTimestamp("intime");
                Timestamp outtime = res.getTimestamp("outtime");
                User user = new User(id,username,phonenumber,password,cardid,intime,outtime);
                list.add(user);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DruidUtil.close(conn,pre,res);
        }
        return list;
    }

    /**
     * 根据用户的id去查
     *
     * @param id
     * @return 返回用户的信息
     */
    @Override
    public User findByID(int id) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement pre = null;
        ResultSet res = null;
        try {
            pre = conn.prepareStatement(SQL_FIND_BY_ID);
            pre.setObject(1,id);
            res = pre.executeQuery();
            if (res.next()){
                String username = res.getString("username");
                String phonenumber = res.getString("phonenumber");
                String cardid = res.getString("cardid");
                String password = res.getString("password");
                Timestamp intime = res.getTimestamp("intime");
                Timestamp outtime = res.getTimestamp("outtime");
                User user = new User(id,username,phonenumber,password,cardid,intime,outtime);
                return user;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,pre,res);
        }
        return null;
    }

    /**
     * 根据手机号码查询
     *
     * @param phonenumber
     * @return 用户
     */
    @Override
    public User findByphonenumber(String phonenumber) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement pre = null;
        ResultSet res = null;
        try {
            pre = conn.prepareStatement(SQL_FIND_BY_phonenumber);
            pre.setObject(1,phonenumber);
            res = pre.executeQuery();
            if (res.next()){
                int id = res.getInt("id");
                String username = res.getString("username");
                String cardid = res.getString("cardid");
                String password = res.getString("password");
                Timestamp intime = res.getTimestamp("intime");
                Timestamp outtime = res.getTimestamp("outtime");
                User user = new User(id,username,phonenumber,password,cardid,intime,outtime);
                return user;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,pre,res);
        }
        return null;
    }

    /**
     * 根据id号去修改快递员的信息
     *
     * @param id
     * @param username
     * @param userphone
     * @return 修改快递员的信息
     */
    @Override
    public boolean updateUsernameUserphone(int id, String username, String userphone) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement pre = null;
        try {
            pre = conn.prepareStatement(SQL_UPDATE_USERNAME_USERPHONE);
            pre.setObject(1,username);
            pre.setObject(2,userphone);
            pre.setObject(3,id);

            return pre.executeUpdate()==0?false:true;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,pre,null);
        }
        return false;
    }

    /**
     * 根据id号去修改用户的信息
     *
     * @param id
     * @param username
     * @param userphone
     * @param password
     * @return 修改用户的信息
     */
    @Override
    public boolean updateUsernameUserphonePassword(int id, String username, String userphone, String password) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement pre = null;
        try {
            pre = conn.prepareStatement(SQL_UPDATE_USERNAME_USERPHONE_PASSWORD);
            pre.setObject(1,username);
            pre.setObject(2,userphone);
            pre.setObject(3,password);
            pre.setObject(4,id);

            return pre.executeUpdate()==0?false:true;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,pre,null);
        }
        return false;
    }

    /**
     * @param user
     * @return 新增用户
     */
    @Override
    public boolean insert(User user) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement pre = null;
        try {
            pre = conn.prepareStatement(SQL_INERT);
            pre.setObject(1,user.getUsername());
            pre.setObject(2,user.getPhonenumber());
            pre.setObject(3,user.getPassword());
            pre.setObject(4,user.getCardid());
            return pre.executeUpdate()==0?false:true;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,pre,null);
        }
        return false;
    }

    /**
     * 根据id号去修改用户的信息
     *
     * @param id
     * @param user
     * @return 修改用户的信息
     */
    @Override
    public boolean update(int id, User user) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement pre = null;
        try {
            pre = conn.prepareStatement(SQL_UPDATE);
            pre.setObject(1,user.getUsername());
            pre.setObject(2,user.getPhonenumber());
            pre.setObject(3,user.getCardid());
            pre.setObject(4,user.getPassword());
            pre.setObject(5,id);

            return pre.executeUpdate()==0?false:true;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,pre,null);
        }
        return false;
    }

    /**
     * 根据手机号，更新用户最近的登陆时间
     *
     * @param userphone
     */
    @Override
    public boolean updatetime(String userphone) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement pre = null;
        try {
            pre = conn.prepareStatement(SQL_UPDATETIME);
            pre.setObject(1,userphone);
            return pre.executeUpdate()==0?false:true;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,pre,null);
        }
        return false;
    }

    /**
     * 根据id去删除用户的信息
     *
     * @param id
     * @return 删除true false
     */
    @Override
    public boolean delete(int id) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement pre = null;
        try {
            pre = conn.prepareStatement(SQL_DELETE);
            pre.setObject(1,id);
            return pre.executeUpdate()==0?false:true;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,pre,null);
        }
        return false;
    }
}
